package com.xishu.util;

import com.xishu.annotation.excel.ExcelCell;
import com.xishu.entity.AbstractEntity;
import com.xishu.handler.MyDataFormatter;
import com.xishu.handler.SheetHandler;
import com.xishu.handler.VouchersHandler;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.List;
import java.util.stream.Collectors;

public class ExcelUtil implements Tools {
    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private static ExcelUtil instance = new ExcelUtil();

    private ExcelUtil() {

    }

    public static ExcelUtil getInstance() {
        return instance;
    }

    /**
     * 创建excel
     */
    public <T extends AbstractEntity> File createExcel(List<T> dataList) throws Exception {
        String sheetName = "Sheet0";
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);

        //创建标题
        createTitle(wb, sheet, dataList.get(0).getClass());

        for (AbstractEntity data : dataList) {
            writeBean(sheet, data);
        }

        File xlsFile = MongoId.getInstance().generateTmpFile("xls");

        logger.info("will write to file {}", xlsFile.getAbsolutePath());
        wb.write(xlsFile);

        return xlsFile;
    }

    /**
     * 创建新的一行
     *
     * @param sheet
     * @return
     */
    public Row createRow(Sheet sheet) {
        int rows = sheet.getPhysicalNumberOfRows();
        return sheet.createRow(rows);
    }

    /**
     * 获取标题的列
     *
     * @param tClass
     * @return
     */
    public List<Field> getTitleFieldList(Class tClass) {
        List<Field> allField = ClassUtil.getAllField(tClass);
        return allField.stream().filter(f -> {
            ExcelCell excelCell = f.getAnnotation(ExcelCell.class);
            return excelCell != null && excelCell.isTitle();
        }).collect(Collectors.toList());
    }

    /**
     * 创建标题
     */
    public void createTitle(HSSFWorkbook wb, Sheet sheet, Class tClass) {
        CellStyle style = createTitleStyle(wb);
        Row row = createRow(sheet);
        List<Field> titleFieldList = getTitleFieldList(tClass);
        int startIndex = 0;

        for (Field field : titleFieldList) {
            Cell cell = row.createCell(startIndex);
            cell.setCellValue(field.getAnnotation(ExcelCell.class).title());
            cell.setCellStyle(style);
            startIndex = startIndex + 1;
        }
    }

    /**
     * 创建标题
     *
     * @param wb
     * @param sheet
     * @param titleList
     */
    public void createTitle(HSSFWorkbook wb, Sheet sheet, List<String> titleList) {
        if (isEmpty(titleList)) {
            return;
        }

        int startIndex = 0;
        CellStyle style = createTitleStyle(wb);
        Row row = createRow(sheet);

        for (String title : titleList) {
            Cell cell = row.createCell(startIndex);
            cell.setCellValue(title);
            cell.setCellStyle(style);
            startIndex = startIndex + 1;
        }
    }

    /**
     * 标题的样式
     *
     * @return
     */
    public CellStyle createTitleStyle(HSSFWorkbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中
        return cellStyle;
    }

    /**
     * 写一个对象到excel里面
     *
     * @param sheet
     * @param t
     * @tparam T
     */
    public void writeBean(HSSFSheet sheet, AbstractEntity t) {
        int rows = sheet.getPhysicalNumberOfRows();
        logger.info("total row is ${}", sheet.getPhysicalNumberOfRows());
        //先创建一行，再依次写入
        Row row = sheet.createRow(rows);

        int startIndex = 0;

        //写数据到excel里面
        List<Field> fieldList = ClassUtil.getAllField(t.getClass()).stream().filter(f -> f.getAnnotation(ExcelCell.class) != null).collect(Collectors.toList());

        for (Field field : fieldList) {
            try {
                Cell cell = row.createCell(startIndex);
                Object value = field.get(t);
                if (value != null) {
                    cell.setCellValue(value.toString());
                } else {
                    cell.setCellValue("");
                }
            } catch (Exception e) {
                logger.error("e", e);
            }
        }
    }

    /**
     * 根据数据来导出成excel
     *
     * @param titleList
     * @param dataList
     */
    public File exportExcel(List<String> titleList, List<List<Object>> dataList) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Sheet0");

        //创建标题
        createTitle(wb, sheet, titleList);
        //写真实数据
        writeData(wb, sheet, dataList);
        String currentDate = DateUtil.format(System.currentTimeMillis(), "yyyy-MM-dd-HH-mm-ss");
        String randomCode = RandomUtil.generateLowerCaseString(4);
        String fileName = String.format("export-%s-%s.xls", currentDate, randomCode);
        File file = MongoId.getInstance().generateTmpFileWithName(fileName);

        wb.write(file);

        return file;
    }

    /**
     * 写数据
     *
     * @param wb
     * @param sheet
     * @param dataList
     */
    public void writeData(HSSFWorkbook wb, Sheet sheet, List<List<Object>> dataList) {
        for (List<Object> rowData : dataList) {
            int startIndex = 0;
            CellStyle style = createTitleStyle(wb);
            Row row = createRow(sheet);
            for (Object data : rowData) {
                Cell cell = row.createCell(startIndex);

                if (data != null) {
                    cell.setCellValue(data.toString());
                }

                cell.setCellStyle(style);
                startIndex = startIndex + 1;
            }
        }
    }

    /**
     * Excel 导入会员数据
     *
     * @param inputStream
     * @param companyId
     * @throws Exception
     */
    public void importExcel(InputStream inputStream, Long companyId) throws Exception {
        OPCPackage open = OPCPackage.open(inputStream);
        XSSFReader reader = new XSSFReader(open);
        SharedStringsTable table = reader.getSharedStringsTable();
        StylesTable stylesTable = reader.getStylesTable();
        XSSFReader.SheetIterator sheetsData = (XSSFReader.SheetIterator) reader.getSheetsData();
        XMLReader xmlReader = XMLReaderFactory.createXMLReader();
        SheetHandler sheetHandler = new SheetHandler();
        sheetHandler.setCompanyId(companyId);
        XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(stylesTable, table, sheetHandler, new MyDataFormatter(), false);
        xmlReader.setContentHandler(xmlHandler);
        while (sheetsData.hasNext()) {
            InputStream next = sheetsData.next();
            InputSource inputSource = new InputSource(next);
            xmlReader.parse(inputSource);
        }
    }


    /**
     * excel 导入优惠券数据
     *
     * @param inputStream
     * @param ticketId
     * @throws Exception
     */

    public void importVoucherExcel(InputStream inputStream, Long ticketId) throws Exception {
        OPCPackage open = OPCPackage.open(inputStream);
        XSSFReader reader = new XSSFReader(open);
        SharedStringsTable table = reader.getSharedStringsTable();
        StylesTable stylesTable = reader.getStylesTable();
        XSSFReader.SheetIterator sheetsData = (XSSFReader.SheetIterator) reader.getSheetsData();
        XMLReader xmlReader = XMLReaderFactory.createXMLReader();
        VouchersHandler sheetHandler = new VouchersHandler();
        sheetHandler.setTicketId(ticketId);
        XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(stylesTable, table, sheetHandler, new MyDataFormatter(), false);
        xmlReader.setContentHandler(xmlHandler);
        while (sheetsData.hasNext()) {
            InputStream next = sheetsData.next();
            InputSource inputSource = new InputSource(next);
            xmlReader.parse(inputSource);
        }
    }

}
